with order_mark as (
    select *
    from (
             select order_id   --订单ID
                  , waybill_id --运单号
                  , input_time --打标录入时间
                  , concat_ws(',', collect_set(field_name)) as order_label
             from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt --订单打标拓展表
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -31)
               and is_delete = 1
               and waybill_id is not null
             group by order_id   --订单ID
                    , waybill_id --运单号
                    , input_time --录入时间
         ) a
    where array_contains(split(order_label, ','), 'ON-DEMAND')
),
     reback_transfer as (
         select waybill_no
         from jms_dwd.dwd_tab_reback_transfer_express_base_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -31)
           and apply_type_code <> 5 --申请类型4:退回  5：转寄
           and status not in (3, 4) --状态 1待审核，2已审核,3 取消申请,4.驳回
         group by waybill_no
     ),
     sign_table as (
         select waybill_no
              , input_time
              , order_source_code_order
              , order_source_name_order
              , first_network_network_code --始发网点网点编码
              , taking_early_scantime      --最早揽收时间
              , taking_early_scansitecode  --最早揽收网点
              , deliver_early_scantime
              , deliver_early_scansitecode
              , instore_early_scantime
              , instore_early_scansitecode
              , outstore_early_scantime
              , outstore_early_scansitecode
              , sign_early_scantime
              , sign_early_scansitecode
              , send_deliver_usercode_early
              , send_deliver_user_early
              , customer_code
              , customer_name
              , coalesce(outstore_early_scantime, sign_early_scantime)         as sign_time
              , coalesce(outstore_early_scansitecode, sign_early_scansitecode) as sign_code
              , is_reback
         from jms_dwd.dwd_wide_unsign_summary_waybill_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -44)
--            and (to_date(instore_early_scantime) = '{{ execution_date | cst_ds }}' or to_date(outstore_early_scantime) = '{{ execution_date | cst_ds }}'
--              or to_date(sign_early_scantime) = '{{ execution_date | cst_ds }}'
--              )
           and coalesce(to_date(outstore_early_scantime), to_date(sign_early_scantime)) =
               '{{ execution_date | cst_ds }}'
     ),
     ops_intervene as (
         select business_type --业务类型
              , data_source
              , waybill_no    --运单号
         from (
                  select business_type --业务类型
                       , data_source
                       , waybill_no    --运单号
                       , row_number() over (partition by waybill_no order by updated_time desc ) as rn
                  from jms_dwd.dwd_tab_ops_intervene_input_dt --干预平台数据表
                  where dt <= '{{ execution_date | cst_ds }}'
                    and dt >= date_add('{{ execution_date | cst_ds }}', -44)
                    and is_delete = 0
                    and business_type in ('shsm', 'tfyz')
              ) a
         where rn = 1
     ),
     door_sign as (
         select *
         from (
                  select waybill_no
                       , scan_site_name --扫描网点code
                       , scan_site_code --扫描网点
                       , timely_type    --1入库,2签收
                       , signed_type    --签收类型
                       , scan_user
                       , scan_user_code
                       , is_door        --是否送货上门1是0否
                       , row_number() over (partition by waybill_no order by dt desc ) as rn
                  from jms_dm.dm_door_sign_rate_detail_dt
                  where dt <= '{{ execution_date | cst_ds }}'
                    and dt >= date_add('{{ execution_date | cst_ds }}', -6)
                    and is_door in (0, 1)
              ) a
         where rn = 1
     ),
     result_table as (
         select t2.order_id                                                                               --订单ID
              , t2.waybill_id                                                                             --运单号
              , t2.order_label
              , t1.order_source_code_order                                                                --订单表-订单来源CODE
              , t1.order_source_name_order                                                                --订单表-订单来源名称
              , t2.input_time                                                                             --打标录入时间
              , t1.deliver_early_scantime                                   as arr_deliver_scantime       --第一次派件时间(到件时间)
              , t1.sign_time                                                as sign_time                  --签收时间
              , coalesce(t1.send_deliver_usercode_early, t3.scan_user_code) as deliver_usercode           --派件业务员code
              , coalesce(t1.send_deliver_user_early, t3.scan_user)          as deliver_username           --派件业务员name
              , coalesce(t1.deliver_early_scansitecode, t3.scan_site_code)  as deliver_scansite_code      --派件网点code
              , t1.taking_early_scansitecode                                as first_network_network_code --始发网点网点code
              , t1.is_reback                                                as is_reback                  --是否退转件，1：是，0：否
              , t1.customer_code                                            as customer_code              --客户编号CODE
              , t1.customer_name                                            as customer_name              --客户编号名称
              , t3.timely_type                                              as actual_delivery_way        --实际派送方式  --1入库,2签收
              , t3.signed_type                                              as sign_type                  --签收类型
              , t3.is_door                                                  as is_door                    --是否送货上门1是0否
         from sign_table t1
                  left anti
                  join reback_transfer t4
                       on t1.waybill_no = t4.waybill_no
                  join order_mark t2
                       on t1.waybill_no = t2.waybill_id
                  left join door_sign t3
                            on t1.waybill_no = t3.waybill_no
     )
insert
overwrite
table
jms_dm.dm_cusc_demand_delivery_waybill_detail_dt
partition
(
dt
)
select t1.order_id                                      --订单ID
     , t1.waybill_id                                    --运单号
     , t1.order_source_code_order                       --订单表-订单来源CODE
     , t1.order_source_name_order                       --订单表-订单来源名称
     , t1.input_time                                    --打标录入时间
     , t1.arr_deliver_scantime                          --第一次派件时间(到件时间)
     , t1.sign_time                                     --签收时间
     , t1.deliver_usercode                              --派件业务员code
     , t1.deliver_username                              --派件业务员name
     , t1.deliver_scansite_code                         --派件网点code
     , t4.name            as deliver_scansite_name      --派件网点name
     , t4.fran_code       as deliver_fran_code          --派件网点加盟商code
     , t4.fran_name       as deliver_fran_name          --派件网点加盟商name
     , t4.agent_code      as deliver_agent_code         --派件网点代理区code
     , t4.agent_name      as deliver_agent_name         --派件网点代理区name
     , t1.first_network_network_code                    --始发网点网点code
     , t5.name            as first_network_network_name --始发网点网点name
     , t5.fran_code       as first_network_fran_code    --始发网点加盟商code
     , t5.fran_name       as first_network_fran_name    --始发网点加盟商name
     , t5.agent_code      as first_network_agent_code   --始发网点代理区code
     , t5.agent_name      as first_network_agent_name   --始发网点代理区name
     , t1.is_reback                                     --是否退转件，1：是，0：否
     , t1.customer_code                                 --客户编号CODE
     , t1.customer_name                                 --客户编号名称
     , case
           when t2.business_type = 'shsm' then '1'
           when t2.business_type = 'tfyz' then '0'
           else null
    end                   as delivery_way               --派送方式  --干预平台数据表
     , case
           when data_source = '客户渠道' then '订单下发'
           when data_source = '智能外呼' then '智能外呼'
           when data_source = '95' then '直接电联'
           else '其他'
    end                   as delivery_source            --派送来源
     , case
           when is_door = 1 then 2
           when is_door = 0 then 1
           else null
    end                   as actual_delivery_way        --实际派送方式
     , t1.sign_type                                     --签收类型
     , case
           when (t2.business_type is null or is_door is null) then 2
           when (t2.business_type = 'shsm' and t1.is_door = 1) or
                (t2.business_type = 'tfyz' and t1.is_door = 0)
               then 1
           else 0 end     as is_performance             --是否履约1:是 0：否
     , to_date(sign_time) as date_time
     , t1.order_label
     , t4.city_id         as deliver_city_id
     , t4.city_desc       as deliver_city_desc
     , to_date(sign_time) as dt
from result_table t1
         left join ops_intervene t2
                   on t1.waybill_id = t2.waybill_no
         left join jms_dim.dim_network_whole_massage t4
                   on t1.deliver_scansite_code = t4.code
         left join jms_dim.dim_network_whole_massage t5
                   on t1.first_network_network_code = t5.code
    distribute by dt
;
